<?php

App::uses('Crud', 'Model');
App::uses('Moe', 'Model');
App::uses('Area', 'Model');
App::uses('Line', 'Model');
App::uses('Workstation', 'Model');
App::uses('WorkstationConfig', 'Model');
App::uses('CertificationDetail', 'Model');
App::uses('UserLine', 'Model');
App::uses('UserArea', 'Model');
App::uses('Categorization', 'Model');
App::uses('User', 'Model');
App::uses('Manager', 'Model');
App::uses('Category', 'Model');
App::uses('LogCertification', 'Model');

class Certification extends Crud
{
    /**
     * Al Agregar/eliminar/actualizar las constantes de los modelos implica ir
     * y actualizar los comentarios en los campos de la tabla asociada a este modelo.
     */
    const SUCCESS_OK = 1;
    const SUCCESS_NOK = 0;
    const STATUS_ENABLED = 1;
    const STATUS_DISABLED = 0;
    const TRAINING_NOK = 0;
    const TRAINING_OK = 1;
    const STATUS_UNCERTIFIED = 0;
    const STATUS_CERTIFIED = 1;
    const STATUS_TRAINING = 2;
    const STATUS_LOCK = 3;
    const STATUS_PROPAGATION = 4;
    const LOCK_OFF = 0;
    const LOCK_ON = 1;
    const ABOUT_EXPIRE = 90;
    const MIGRATED_ON = 1;
    const MIGRATED_OFF = 0;

    public static function getTypes()
    {
        return array(
            array('value' => self::STATUS_UNCERTIFIED, 'text' => __('Sin certificar')),
            array('value' => self::STATUS_TRAINING, 'text' => __('En entrenamiento')),
            array('value' => self::STATUS_CERTIFIED, 'text' => __('Certificado')),
            array('value' => self::STATUS_LOCK, 'text' => __('Bloqueado')),
        );
    }

    /**
     * Desactivamos todas las certificaciones para un usuario y una linea
     */
    public function deleteCertificationsByUserAndLine($lineId, $userId = null)
    {
        $filters = array(
            'fields' => array(
                'Certification.id',
                '"' . self::STATUS_DISABLED . '" as status',
            ),
            'joins' => array(
                array(
                    'table' => 'workstations',
                    'alias' => 'w',
                    'type' => 'INNER',
                    'conditions' => array(
                        'w.id = Certification.workstation_id',
                        'w.line_id' => $lineId,
                    ),
                ),
            ),
            'conditions' => array(
                'Certification.status' => self::STATUS_ENABLED,
            ),
        );
        if ($userId !== null) {
            $filters['conditions']['Certification.user_id'] = $userId;
        }
        $certifications = $this->flatArray($this->find('all', $filters));
        $this->saveAll($certifications, array(
            'fieldList' => array(
                'Certification' => array('status'),
            )));
    }

    public function getCertificationWithDetails($certificationId)
    {
        $filters = array(
            'fields' => array(
                /// Operación
                'w.id as wId',
                'w.description as wDescription',
                'w.life as wLife',
                'w.days as wDays',
                /// Usuario
                'u.id as uId',
                'u.full_name as uName',
                /// Certificación
                'Certification.id as cId',
                'Certification.start as cStart',
                'Certification.end as cEnd',
                'Certification.life as cLife',
                'Certification.training as cTraining',
            ),
            'joins' => array(
                array(
                    'table' => 'workstations',
                    'alias' => 'w',
                    'type' => 'INNER',
                    'conditions' => array(
                        'w.id = Certification.workstation_id',
                    ),
                ),
                array(
                    'table' => 'us3rs_r4gg14n4',
                    'alias' => 'u',
                    'type' => 'INNER',
                    'conditions' => array(
                        'u.id = Certification.user_id',
                    ),
                ),
            ),
            'conditions' => array(
                'Certification.id' => $certificationId,
            ),
        );
        $certifications = $this->flatArray($this->find('all', $filters));
        return $certifications[0];
    }

    /**
     * Contamos las certificaciones exitosas y que no han caducado para un usuario
     * y una linea de producción
     * @param string $userId UUID
     * @param string $lineId UUID
     * @return integer
     */
    public function countCertificationsByUserAndLine($userId, $lineId)
    {
        $filters = array(
            'joins' => array(
                array(
                    'table' => 'workstations',
                    'alias' => 'w',
                    'type' => 'INNER',
                    'conditions' => array(
                        'Certification.workstation_id = w.id',
                        'w.status' => Workstation::STATUS_ENABLED,
                    ),
                ),
            ),
            'conditions' => array(
                'Certification.status' => self::STATUS_ENABLED,
                'Certification.success' => self::SUCCESS_OK,
                'Certification.user_id' => $userId,
                'w.line_id' => $lineId,
                'Certification.life >= DATE(NOW())',
            ),
            'group' => array(
                'Certification.user_id',
                'Certification.workstation_id',
            ),
        );
        return $this->find('count', $filters);
    }

    /**
     * Buscamos una certificacion para un usuario y una Operación
     * @param string $userId UUID
     * @param string $workstationId UUID
     * @return array
     */
    public function getEnabledByUserAndWorkstation($userId, $workstationId)
    {
        $filters = array(
            'conditions' => array(
                'user_id' => $userId,
                'workstation_id' => $workstationId,
                'status' => self::STATUS_ENABLED
            ),
        );
        return $this->find('first', $filters);
    }

    /**
     * Obtenemos los registros para el grid
     * @return array
     */
    public function getEnabled($uid = 0, $lineId) {
        $filters = array(
            'fields' => array(
                'Certification.id as value',
                'workstations.description as workstation',
                'lines.name as line',
                'Certification.start as start',
                'Certification.end as end',
                'Certification.life as life',
                'DATEDIFF(Certification.life,CURDATE()) < ' . self::ABOUT_EXPIRE . ' as proximos'
            ),
            'joins' => array(
                array(
                    'table' => 'workstations',
                    'type' => 'inner',
                    'conditions' => array(
                        'Certification.workstation_id = workstations.id',
                        'workstations.status' => Workstation::STATUS_ENABLED
                    )
                ),
                array(
                    'table' => 'lines',
                    'type' => 'inner',
                    'conditions' => array(
                        'workstations.line_id = lines.id',
                        'lines.status' => Line::STATUS_ENABLED
                    )
                )
            ),
            'conditions' => array(
                'Certification.user_id' => $uid,
                'Certification.status' => self::STATUS_ENABLED,
                'Certification.success' => self::SUCCESS_OK
            ),
            'order' => array(
                'lines.name ASC',
                'workstations.name ASC'
            ),
        );
        if ( $lineId != null ) {
           $filters['conditions']['lines.id'] = $lineId;
        }
        $records = $this->find('all', $filters);
        return $this->flatArray($records);
    }

    public function getEnabledAbout($uid = 0)
    {
        $filters = array(
            'fields' => array(
                'Certification.id as value',
                'workstations.description as workstation',
                'lines.name as line',
                'Certification.start as start',
                'Certification.end as end',
                'Certification.life as life',
                'DATEDIFF(Certification.life,CURDATE()) as proximos'
            ),
            'joins' => array(
                array(
                    'table' => 'workstations',
                    'type' => 'inner',
                    'conditions' => array('Certification.workstation_id = workstations.id')
                ),
                array(
                    'table' => 'lines',
                    'type' => 'inner',
                    'conditions' => array('workstations.line_id = lines.id')
                )
            ),
            'conditions' => array(
                "Certification.user_id" => $uid,
                "Certification.status" => self::STATUS_ENABLED,
                "Certification.success" => self::SUCCESS_OK,
                "DATEDIFF(Certification.life,CURDATE()) < " . self::ABOUT_EXPIRE
            ),
            'order' => array(
                'proximos ASC'
            ),
        );
        $records = $this->find('all', $filters);
        return $this->flatArray($records);
    }

    public function getByIdWithDetails($id)
    {
        $filters = array(
            'fields' => array(
                'Certification.id',
                'areas.name as area',
                'lines.name as line',
                'workstations.description as workstation',
                'us3rs_r4gg14n4.full_name as user',
                'Certification.end',
                'Certification.life',
            ),
            'joins' => array(
                array(
                    'table' => 'workstations',
                    'type' => 'inner',
                    'conditions' => array('Certification.workstation_id = workstations.id')
                ),
                array(
                    'table' => 'lines',
                    'type' => 'inner',
                    'conditions' => array('workstations.line_id = lines.id')
                ),
                array(
                    'table' => 'areas',
                    'type' => 'inner',
                    'conditions' => array('lines.area_id = areas.id')
                ),
                array(
                    'table' => 'us3rs_r4gg14n4',
                    'type' => 'inner',
                    'conditions' => array('us3rs_r4gg14n4.id = Certification.user_id')
                )
            ),
            'conditions' => array(
                'Certification.id' => $id,
            )
        );
        return $this->flatArray($this->find('all', $filters));
    }

    public function getDataForPassport($moeId, $areaId, $lineId, $payrolls)
    {
        $filters = array(
            'fields' => array(
                'm.name as mname',
                'a.name as aname',
                'l.name as lname',
                'l.id as lid',
                'us.id as uid',
                'us.nomina as numNom',
                'us.full_name as nameAsc',
                'w.description as nameWork',
                'Certification.success as sucss',
                'Certification.training as train',
                'Certification.life as life',
                'Certification.end as end',
                'cat.icon as icon',
                'cat.colour as color',
                'DATEDIFF(Certification.life,CURDATE()) as proximos'
            ),
            'joins' => array(
                array(
                    'table' => 'us3rs_r4gg14n4',
                    'alias' => 'us',
                    'type' => 'inner',
                    'conditions' => array(
                        'OR' => array('us.nomina' => $payrolls)
                    )
                ),
                array(
                    'table' => 'categories',
                    'alias' => 'c',
                    'type' => 'inner',
                    'conditions' => array(
                        'us.category_id = c.id'
                    )
                ),
                array(
                    'table' => 'moes',
                    'alias' => 'm',
                    'type' => 'inner',
                    'conditions' => array(
                        'm.status' => Moe::STATUS_ENABLED
                    )
                ),
                array(
                    'table' => 'areas',
                    'alias' => 'a',
                    'type' => 'inner',
                    'conditions' => array(
                        'a.moe_id = m.id',
                        'a.status' => Area::STATUS_ENABLED
                    )
                ),
                array(
                    'table' => 'lines',
                    'alias' => 'l',
                    'type' => 'inner',
                    'conditions' => array(
                        'l.area_id = a.id',
                        'l.status' => Line::STATUS_ENABLED
                    )
                ),
                array(
                    'table' => 'workstations',
                    'alias' => 'w',
                    'type' => 'inner',
                    'conditions' => array(
                        'w.line_id = l.id',
                        'w.status' => Workstation::STATUS_ENABLED
                    )
                ),
                array(
                    'table' => 'user_lines',
                    'alias' => 'ul',
                    'type' => 'inner',
                    'conditions' => array(
                        'ul.user_id = us.id',
                        'w.line_id = ul.line_id',
                        'ul.status' => UserLine::STATUS_ENABLED
                    )
                ),
                array(
                    'table' => 'categorizations',
                    'alias' => 'cat',
                    'type' => 'inner',
                    'conditions' => array(
                        'cat.id = ul.level',
                        'cat.status' => Categorization::STATUS_ENABLED
                    )
                )
            ),
            'conditions' => array(
                'Certification.user_id = us.id',
                'c.name = "Asociado"',
                'Certification.workstation_id = w.id',
                'OR' => array('Certification.success = 1', 'Certification.training = 1'),
                'Certification.status' => self::STATUS_ENABLED,
            ),
            'order' => array('l.id', 'us.id', 'Certification.training', 'proximos')
        );

        if ($moeId != '0') {
            $filters['conditions']['m.id'] = $moeId;
        }
        if ($areaId != '0') {
            $filters['conditions']['a.id'] = $areaId;
        }
        if ($lineId != '0') {
            $filters['conditions']['l.id'] = $lineId;
        }

        return $this->flatArray($this->find('all', $filters));
    }

    public function getInfoCertificates()
    {
        $filters = array(
            'fields' => array(
                'Certification.id as idCert',
                'us.full_name as nameU',
                'CONCAT(a.name, " ", l.name, " ", w.description) as nameW',
                'Certification.end as date',
                'w.image as img',
                'mgrh.signature as rh_sign',
                'mgrh.job as rh_job',
                'urh.full_name as rh_name',
                'mgmoe.signature as moe_sign',
                'mgmoe.job as moe_job',
                'umoe.full_name as moe_name',
                'mgts.signature as ts_sign',
                'mgts.job as ts_job',
                'uts.full_name as ts_name'
            ),
            'joins' => array(
                array(
                    'table' => 'us3rs_r4gg14n4',
                    'alias' => 'us',
                    'type' => 'inner',
                    'conditions' => array(
                        'us.id = Certification.user_id',
                        'us.status' => User::STATUS_ENABLED
                    )
                ),
                array(
                    'table' => 'workstations',
                    'alias' => 'w',
                    'type' => 'inner',
                    'conditions' => array(
                        'Certification.workstation_id = w.id',
                        'w.status' => Workstation::STATUS_ENABLED
                    )
                ),
                array(
                    'table' => 'lines',
                    'alias' => 'l',
                    'type' => 'inner',
                    'conditions' => array(
                        'l.id = w.line_id',
                        'l.status' => Line::STATUS_ENABLED
                    )
                ),
                array(
                    'table' => 'areas',
                    'alias' => 'a',
                    'type' => 'inner',
                    'conditions' => array(
                        'a.id = l.area_id',
                        'a.status' => Area::STATUS_ENABLED
                    )
                ),
                array(
                    'table' => 'moes',
                    'alias' => 'm',
                    'type' => 'inner',
                    'conditions' => array(
                        'm.id = a.moe_id',
                        'm.status' => Moe::STATUS_ENABLED
                    )
                ),
                array(
                    'table' => 'managers',
                    'alias' => 'mgrh',
                    'type' => 'left',
                    'conditions' => array(
                        'mgrh.type' => Manager::MANAGER_RH,
                        'mgrh.status' => Manager::STATUS_ENABLED
                    )
                ),
                array(
                    'table' => 'us3rs_r4gg14n4',
                    'alias' => 'urh',
                    'type' => 'left',
                    'conditions' => array(
                        'urh.id = mgrh.user_id',
                        'urh.status' => User::STATUS_ENABLED
                    )
                ),
                array(
                    'table' => 'managers',
                    'alias' => 'mgmoe',
                    'type' => 'left',
                    'conditions' => array(
                        'mgmoe.moe_id = m.id',
                        'mgmoe.type' => Manager::MANAGER_MOE,
                        'mgmoe.status' => Manager::STATUS_ENABLED
                    )
                ),
                array(
                    'table' => 'us3rs_r4gg14n4',
                    'alias' => 'umoe',
                    'type' => 'left',
                    'conditions' => array(
                        'umoe.id = mgmoe.user_id',
                        'umoe.status' => User::STATUS_ENABLED
                    )
                ),
                array(
                    'table' => 'managers',
                    'alias' => 'mgts',
                    'type' => 'left',
                    'conditions' => array(
                        'mgts.moe_id = m.id',
                        'mgts.type' => Manager::TRAINING_SUPERVISOR,
                        'mgts.status' => Manager::STATUS_ENABLED
                    )
                ),
                array(
                    'table' => 'us3rs_r4gg14n4',
                    'alias' => 'uts',
                    'type' => 'left',
                    'conditions' => array(
                        'uts.id = mgts.user_id',
                        'uts.status' => User::STATUS_ENABLED
                    )
                )
            ),
            'conditions' => array(
                'Certification.status' => self::STATUS_ENABLED,
                'Certification.success' => 1
            )
        );

        return $this->flatArray($this->find('all', $filters));
    }

    public function getCertificationsForTraining($user_id)
    {
        $filters = array(
            'fields' => array(
                'id',
                'workstation_id',
                'start',
                'end',
                'status',
            ),
            'conditions' => array(
                'user_id' => $user_id,
                'training' => self::STATUS_TRAINING,
                'lock' => self::LOCK_OFF,
                'status' => self::STATUS_ENABLED
            ),
        );
        return $this->flatArray($this->find('all', $filters));
    }

    public function getForExam($user_id, $ws_id)
    {
        $filters = array(
            'conditions' => array(
                'user_id' => $user_id,
                'workstation_id' => $ws_id,
                'training' => 1,//self::STATUS_TRAINING,
                'lock' => self::LOCK_OFF,
                'status' => self::STATUS_ENABLED
            )
        );
        $resCer = $this->find('first', $filters);
        return $resCer['Certification'];
    }

    public function getLeftForCertification($certificationId)
    {
        $filters = array(
            'fields' => array(
                'wc.name as workstation_config_name'
            ),
            'joins' => array(
                array(
                    'table' => 'workstations',
                    'alias' => 'w',
                    'type' => 'INNER',
                    'conditions' => array(
                        'w.id = Certification.workstation_id',
                        'w.status' => Workstation::STATUS_ENABLED
                    ),
                ),
                array(
                    'table' => 'workstations_configs',
                    'alias' => 'wc',
                    'type' => 'INNER',
                    'conditions' => array(
                        'wc.workstation_id = w.id',
                        'wc.status' => WorkstationConfig::STATUS_ENABLED
                    ),
                ),
                array(
                    'table' => 'certifications_details',
                    'alias' => 'cd',
                    'type' => 'LEFT',
                    'conditions' => array(
                        'wc.id = cd.workstation_config_id',
                        'cd.certification_id = Certification.id',
                        'cd.status' => CertificationDetail::STATUS_ENABLED
                    ),
                ),
            ),
            'conditions' => array(
                'Certification.id' => $certificationId,
                'cd.id IS NULL',
                'Certification.status' => self::STATUS_ENABLED
            ),
        );
        return $this->flatArray($this->find('all', $filters));
    }

    public function getByLine($lineId)
    {
        $filters = array(
            'fields' => array(),
            'joins' => array(
                array(
                    'table' => 'workstations',
                    'alias' => 'w',
                    'type' => 'inner',
                    'conditions' => array(
                        'w.status' => Workstation::STATUS_ENABLED,
                        'w.line_id' => $lineId
                    ),
                ),
            ),
            'conditions' => array(
                'Certification.status' => self::STATUS_ENABLED,
                'Certification.success' => self::SUCCESS_OK,
            ),
        );
        return $this->flatArray($this->find('all', $filters));
    }

    function getEnabledNextToExpireByUser($userId) {
        $filters = array(
            'fields' => array(
                'Certification.id as value',
                'a.name as area',
                'workstations.description as workstation',
                'lines.name as line',
                'Certification.life as end',
                'Certification.start as min_end',
                'u.nomina as nomina',
                'u.full_name as name'
            ),
            'joins' => array(
                array(
                    'table' => 'user_areas',
                    'alias' => 'ua',
                    'type' => 'inner',
                    'conditions' => array(
                        'ua.user_id' => $userId,
                        'ua.status' => UserArea::STATUS_ENABLED
                    )
                ),
                array(
                    'table' => 'us3rs_r4gg14n4',
                    'alias' => 'u',
                    'type' => 'inner',
                    'conditions' => array(
                        'u.id = Certification.user_id',
                        'u.status' => User::STATUS_ENABLED
                    )
                ),
                array(
                    'table' => 'areas',
                    'alias' => 'a',
                    'type' => 'inner',
                    'conditions' => array(
                        'a.id = ua.area_id',
                        'a.status' => Area::STATUS_ENABLED
                    )
                ),
                array(
                    'table' => 'lines',
                    'type' => 'inner',
                    'conditions' => array(
                        'lines.area_id = a.id'
                    )
                ),
                array(
                    'table' => 'workstations',
                    'type' => 'inner',
                    'conditions' => array(
                        'workstations.line_id = lines.id',
                        'workstations.status' => Workstation::STATUS_ENABLED
                    )
                ),
            ),
            'conditions' => array(
                'Certification.workstation_id = workstations.id',
                'DATEDIFF(Certification.life,CURDATE()) < workstations.notification_end',
                'Certification.status' => self::STATUS_ENABLED,
                'Certification.success' => self::SUCCESS_OK
            ),
            'order' => array(
                'area ASC',
                'line ASC',
                'workstation ASC',
                'end ASC',
            )
        );

        $records = $this->find('all', $filters);
        return $this->flatArray($records);
    }
    
    public function getDetails($certificationId){
        $filters = array(
            'fields' => array(
                "Certification.user_id",
                'wc.id as workstation_config_id',
                'wc.name as workstation_config_name',
                'wc.minimum_qualification as workstation_config_qualification',
                'cd.id as certification_detail_id',
                'cd.qualification as certification_detail_qualification',
                'cd.certification_date as certification_detail_date',
                'TIMESTAMPDIFF(MINUTE, cd.creation_date, now()) as minutes',
                'cd.creation_date as certification_detail_creation_date',
            ),
            'joins' => array(
                array(
                    'table' => 'workstations_configs',
                    'alias' => 'wc',
                    'type' => 'INNER',
                    'conditions' => array(
                        'Certification.workstation_id = wc.workstation_id',
                        'wc.status' => WorkstationConfig::STATUS_ENABLED,
                    ),
                ),
                array(
                    'table' => 'certifications_details',
                    'alias' => 'cd',
                    'type' => 'LEFT',
                    'conditions' => array(
                        'cd.workstation_config_id = wc.id',
                        'cd.certification_id' => $certificationId,
                        'cd.status' => CertificationDetail::STATUS_ENABLED,
                    ),
                ),
            ),
            'conditions' => array(
                'Certification.id' => $certificationId,
            ),
            'order' => array(
                'wc.orden' => 'ASC',
            ),
        );
        $records = $this->find('all', $filters);
        return $this->flatArray($records);
    }

    public function getCertificationsForKardex($userId) {
        $filters = array(
            'fields' => array(
                'm.name as moe',
                'a.name as area',
                'l.name as line',
                'w.description as work',
                'Certification.start as start',
                'Certification.end as end',
                'Certification.life as life',
                'Certification.success as success',
                'Certification.training as training',
                'IF(cd.qualification >= wc.minimum_qualification, 1, 0) as EvPisoAp',
                'cd.qualification as EvPiso',
                'IF(cd2.qualification >= wc2.minimum_qualification, 1, 0) as examCertAp',
                'cd2.qualification as examCert',
                'IF(cd3.qualification >= wc3.minimum_qualification, 1, 0) as CertPisoAp',
                'cd3.qualification as CertPiso',
            ),
            'joins' => array(
                array(
                    'table' => 'workstations',
                    'alias' => 'w',
                    'type' => 'inner',
                    'conditions' => array(
                        'w.id = Certification.workstation_id',
                        'w.status' => Workstation::STATUS_ENABLED
                    )
                ),
                array(
                    'table' => 'lines',
                    'alias' => 'l',
                    'type' => 'inner',
                    'conditions' => array(
                        'l.id = w.line_id',
                        'l.status' => Line::STATUS_ENABLED
                    )
                ),
                array(
                    'table' => 'areas',
                    'alias' => 'a',
                    'type' => 'inner',
                    'conditions' => array(
                        'a.id = l.area_id',
                        'a.status' => Area::STATUS_ENABLED
                    )
                ),
                array(
                    'table' => 'moes',
                    'alias' => 'm',
                    'type' => 'inner',
                    'conditions' => array(
                        'm.id = a.moe_id',
                        'm.status' => Moe::STATUS_ENABLED
                    )
                ),
                array(
                    'table' => 'certifications_details',
                    'alias' => 'cd',
                    'type' => 'left',
                    'conditions' => array(
                        'cd.certification_id = Certification.id',
                        'cd.status' => CertificationDetail::STATUS_ENABLED
                    )
                ),
                array(
                    'table' => 'workstations_configs',
                    'alias' => 'wc',
                    'type' => 'left',
                    'conditions' => array(
                        'wc.workstation_id = w.id',
                        'wc.id = cd.workstation_config_id',
                        'wc.name = "Evaluación en piso"',
                        'wc.status' => WorkstationConfig::STATUS_ENABLED
                    )
                ),
                array(
                    'table' => 'certifications_details',
                    'alias' => 'cd2',
                    'type' => 'left',
                    'conditions' => array(
                        'cd2.certification_id = Certification.id',
                        'cd2.status' => CertificationDetail::STATUS_ENABLED
                    )
                ),
                array(
                    'table' => 'workstations_configs',
                    'alias' => 'wc2',
                    'type' => 'left',
                    'conditions' => array(
                        'wc2.workstation_id = w.id',
                        'wc2.id = cd2.workstation_config_id',
                        'wc2.name = "Certificación en piso"',
                        'wc2.status' => WorkstationConfig::STATUS_ENABLED
                    )
                ),
                array(
                    'table' => 'certifications_details',
                    'alias' => 'cd3',
                    'type' => 'left',
                    'conditions' => array(
                        'cd3.certification_id = Certification.id',
                        'cd3.status' => CertificationDetail::STATUS_ENABLED
                    )
                ),
                array(
                    'table' => 'workstations_configs',
                    'alias' => 'wc3',
                    'type' => 'left',
                    'conditions' => array(
                        'wc3.workstation_id = w.id',
                        'wc3.id = cd3.workstation_config_id',
                        'wc3.name = "Validación en piso"',
                        'wc3.status' => WorkstationConfig::STATUS_ENABLED
                    )
                ),
            ),
            'conditions' => array(
                'Certification.user_id' => $userId,
                'Certification.status' => self::STATUS_ENABLED
            ),
            'order' => array(
                'end ASC'
            ),
            'group' => array(
                'Certification.id'
            )
        );

        $records = $this->find('all', $filters);
        return $this->flatArray($records);
    }

    public function getPassportsControlByArea($areaId, $filterSuccess)
    {
        $filters = array(
            'fields' => array(
                'us.nomina as nomina',
                'us.full_name as name',
                'm.name as moe',
                'a.name as area',
                'l.name as line',
                'us2.full_name as supervisor',
                'w.description as work',
                'Certification.start as start',
                'Certification.life as life',
                'Certification.end as end',
                'lc.end as startExt',
                'IF(lc.end IS NULL, "", Certification.end) as endExt',
                'lc2.log_comment as comments'
            ),
            'joins' => array(
                array(
                    'table' => 'us3rs_r4gg14n4',
                    'alias' => 'us',
                    'type' => 'inner',
                    'conditions' => array(
                        'Certification.user_id = us.id',
                        'us.category_id' => Category::ASOCIADO,
                        'us.status' => User::STATUS_ENABLED
                    )
                ),
                array(
                    'table' => 'workstations',
                    'alias' => 'w',
                    'type' => 'inner',
                    'conditions' => array(
                        'w.id = Certification.workstation_id',
                        'w.status' => Workstation::STATUS_ENABLED
                    )
                ),
                array(
                    'table' => 'lines',
                    'alias' => 'l',
                    'type' => 'inner',
                    'conditions' => array(
                        'l.id = w.line_id',
                        'l.status' => Line::STATUS_ENABLED
                    )
                ),
                array(
                    'table' => 'areas',
                    'alias' => 'a',
                    'type' => 'inner',
                    'conditions' => array(
                        'a.id = l.area_id',
                        'a.status' => Area::STATUS_ENABLED
                    )
                ),
                array(
                    'table' => 'moes',
                    'alias' => 'm',
                    'type' => 'inner',
                    'conditions' => array(
                        'm.id = a.moe_id',
                        'm.status' => Moe::STATUS_ENABLED
                    )
                ),
                array(
                    'table' => 'log_certifications',
                    'alias' => 'lc',
                    'type' => 'left',
                    'conditions' => array(
                        'lc.record_id = Certification.id',
                        'lc.log_type' => LogCertification::TYPE_CREATE
                    )
                ),
                array(
                    'table' => 'managers',
                    'alias' => 'man',
                    'type' => 'left',
                    'conditions' => array(
                        'man.moe_id = m.id',
                        'man.status' => Manager::STATUS_ENABLED
                    )
                ),
                array(
                    'table' => 'us3rs_r4gg14n4',
                    'alias' => 'us2',
                    'type' => 'left',
                    'conditions' => array(
                        'us2.id = man.user_id',
                        'us2.status' => User::STATUS_ENABLED
                    )
                ),
                array(
                    'table' => 'log_certifications',
                    'alias' => 'lc2',
                    'type' => 'left',
                    'conditions' => array(
                        'lc2.record_id = Certification.id',
                        'lc2.log_type' => LogCertification::TYPE_UPDATE
                    )
                ),
                array(
                    'table' => 'log_certifications',
                    'alias' => 'lc3',
                    'type' => 'left',
                    'conditions' => array(
                        'lc2.record_id = lc3.record_id',
                        'lc2.log_creation_date < lc3.log_creation_date',
                        'lc3.log_type' => LogCertification::TYPE_UPDATE
                    )
                )
            ),
            'conditions' => array(
                'a.id' => $areaId,
                '(lc2.id IS NULL OR lc3.log_creation_date IS NULL)',
                'Certification.status' => self::STATUS_ENABLED,
            ),
        );

        if ($filterSuccess == true) {
            $filters['conditions'][] = '(Certification.success = 1 OR Certification.training = 1)';
        } else {
            $filters['conditions'][] = 'Certification.success = 0 AND Certification.training = 1';
        }

        return $this->flatArray($this->find('all', $filters));
    }
}
